﻿/*
 * 1. 将数据结构转化为对应的sql语句
 * 
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace HousingDistribution
{
    class DataStruct2SQL
    {
        public string InsertSql(PersonInfo personInfo)
        {
            string sqlHeader = "insert into person (name, type, sex, unit, adminDuty, adminDutyTime, techRank, techRankTime, techTitle, techTitleTime, curRankBeforeTime, workTime, schoolTime, signupTime, retirementTime, mateName, mateRank, mateUnit, remarks, nameInitial, housingEligible, bothEmployee, evaluation, curHousingAddr, housingInfo, formerHousingId, marital, curHousingArea, workStatus, withArmy, mateNameInitial) ";

            StringBuilder sql = new StringBuilder(sqlHeader, 512);

            sql.Append("values ('");
            sql.Append(personInfo.name);
            sql.Append("', '");
            sql.Append((int)personInfo.type);
            sql.Append("', '");
            sql.Append(personInfo.sex);
            sql.Append("', '");
            sql.Append(personInfo.unit);
            sql.Append("', '");
            sql.Append(personInfo.adminDuty);
            sql.Append("', '");
            sql.Append(personInfo.adminDutyTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(personInfo.techRank);
            sql.Append("', '");
            sql.Append(personInfo.techRankTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(personInfo.techTitle);
            sql.Append("', '");
            sql.Append(personInfo.techTitleTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(personInfo.curRankBeforeTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(personInfo.workTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(personInfo.schoolTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(personInfo.signupTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(personInfo.retirementTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(personInfo.mateInfo.name);
            sql.Append("', '");
            sql.Append(personInfo.mateInfo.rank);
            sql.Append("', '");
            sql.Append(personInfo.mateInfo.unit);
            sql.Append("', '");
            sql.Append(personInfo.remarks);
            sql.Append("', '");
            sql.Append(Spell.MakeSpellCode(personInfo.name, SpellOptions.FirstLetterOnly));
            sql.Append("', '");
            sql.Append(personInfo.housingEligible);
            sql.Append("', '");
            sql.Append(personInfo.bothEmployee);
            sql.Append("', '");
            sql.Append(personInfo.evaluation);
            sql.Append("', '");
            sql.Append(personInfo.curHousingAddr.addr);
            sql.Append("', '");
            sql.Append(GetHousingInfoString(personInfo.housingInfo));
            sql.Append("', '");
            sql.Append(GetFormerHousingIdString(personInfo.formerHousingId));
            sql.Append("', '");
            sql.Append((int)personInfo.marital);
            sql.Append("', '");
            sql.Append(personInfo.curHousingAddr.area);
            sql.Append("', '");
            sql.Append((int)personInfo.workStatus);
            sql.Append("', '");
            sql.Append((int)personInfo.withArmy);
            sql.Append("', '");
            sql.Append(Spell.MakeSpellCode(personInfo.mateInfo.name, SpellOptions.FirstLetterOnly));
            sql.Append("')");

            return sql.ToString();
        }

        //更新操作均不包含姓名首字母字段的更新
        public string UpdateTotal(PersonInfo personInfo)
        {
            string sqlHeader = "update person set ";

            StringBuilder sql = new StringBuilder(sqlHeader, 512);

            sql.Append("name ='");
            sql.Append(personInfo.name);
            sql.Append("', type ='");
            sql.Append((int)personInfo.type);
            sql.Append("', sex ='");
            sql.Append(personInfo.sex);
            sql.Append("', unit ='");
            sql.Append(personInfo.unit);
            sql.Append("', adminDuty ='");
            sql.Append(personInfo.adminDuty);
            sql.Append("', adminDutyTime ='");
            sql.Append(personInfo.adminDutyTime.ToShortDateString());
            sql.Append("', techRank ='");
            sql.Append(personInfo.techRank);
            sql.Append("', techRankTime ='");
            sql.Append(personInfo.techRankTime.ToShortDateString());
            sql.Append("', techTitle ='");
            sql.Append(personInfo.techTitle);
            sql.Append("', techTitleTime ='");
            sql.Append(personInfo.techTitleTime.ToShortDateString());
            sql.Append("', curRankBeforeTime ='");
            sql.Append(personInfo.curRankBeforeTime.ToShortDateString());
            sql.Append("', workTime ='");
            sql.Append(personInfo.workTime.ToShortDateString());
            sql.Append("', schoolTime ='");
            sql.Append(personInfo.schoolTime.ToShortDateString());
            sql.Append("', signupTime ='");
            sql.Append(personInfo.signupTime.ToShortDateString());
            sql.Append("', retirementTime ='");
            sql.Append(personInfo.retirementTime.ToShortDateString());
            sql.Append("', mateName ='");
            sql.Append(personInfo.mateInfo.name);
            sql.Append("', mateRank ='");
            sql.Append(personInfo.mateInfo.rank);
            sql.Append("', mateUnit ='");
            sql.Append(personInfo.mateInfo.unit);
            sql.Append("', remarks ='");
            sql.Append(personInfo.remarks);
            sql.Append("', nameInitial ='");
            sql.Append(Spell.MakeSpellCode(personInfo.name, SpellOptions.FirstLetterOnly));
            sql.Append("', housingEligible ='");
            sql.Append(personInfo.housingEligible);
            sql.Append("', bothEmployee ='");
            sql.Append(personInfo.bothEmployee);
            sql.Append("', evaluation ='");
            sql.Append(personInfo.evaluation);
            sql.Append("', curHousingAddr ='");
            sql.Append(personInfo.curHousingAddr.addr);
            sql.Append("', housingInfo ='");
            sql.Append(GetHousingInfoString(personInfo.housingInfo));
            sql.Append("', formerHousingId ='");
            sql.Append(GetFormerHousingIdString(personInfo.formerHousingId));
            sql.Append("', marital ='");
            sql.Append((int)personInfo.marital);
            sql.Append("', curHousingArea ='");
            sql.Append(personInfo.curHousingAddr.area);
            sql.Append("', workStatus ='");
            sql.Append((int)personInfo.workStatus);
            sql.Append("', withArmy ='");
            sql.Append((int)personInfo.withArmy);
            sql.Append("', mateNameInitial ='");
            sql.Append(Spell.MakeSpellCode(personInfo.mateInfo.name, SpellOptions.FirstLetterOnly));
            sql.Append("' where idperson ='");
            sql.Append(personInfo.idperson.ToString());
            sql.Append("'");

            return sql.ToString();
        }

        public string UpdateExceptHousing(PersonInfo personInfo)
        {
            StringBuilder sql = new StringBuilder("update person set ", 512);

            sql.Append("name ='");
            sql.Append(personInfo.name);
            sql.Append("', type ='");
            sql.Append((int)personInfo.type);
            sql.Append("', sex ='");
            sql.Append(personInfo.sex);
            sql.Append("', unit ='");
            sql.Append(personInfo.unit);
            sql.Append("', adminDuty ='");
            sql.Append(personInfo.adminDuty);
            sql.Append("', adminDutyTime ='");
            sql.Append(personInfo.adminDutyTime.ToShortDateString());
            sql.Append("', techRank ='");
            sql.Append(personInfo.techRank);
            sql.Append("', techRankTime ='");
            sql.Append(personInfo.techRankTime.ToShortDateString());
            sql.Append("', techTitle ='");
            sql.Append(personInfo.techTitle);
            sql.Append("', techTitleTime ='");
            sql.Append(personInfo.techTitleTime.ToShortDateString());
            sql.Append("', curRankBeforeTime ='");
            sql.Append(personInfo.curRankBeforeTime.ToShortDateString());
            sql.Append("', workTime ='");
            sql.Append(personInfo.workTime.ToShortDateString());
            sql.Append("', schoolTime ='");
            sql.Append(personInfo.schoolTime.ToShortDateString());
            sql.Append("', signupTime ='");
            sql.Append(personInfo.signupTime.ToShortDateString());
            sql.Append("', retirementTime ='");
            sql.Append(personInfo.retirementTime.ToShortDateString());
            sql.Append("', mateName ='");
            sql.Append(personInfo.mateInfo.name);
            sql.Append("', mateRank ='");
            sql.Append(personInfo.mateInfo.rank);
            sql.Append("', mateUnit ='");
            sql.Append(personInfo.mateInfo.unit);
            sql.Append("', remarks ='");
            sql.Append(personInfo.remarks);
            sql.Append("', nameInitial ='");
            sql.Append(Spell.MakeSpellCode(personInfo.name, SpellOptions.FirstLetterOnly));
            sql.Append("', housingEligible ='");
            sql.Append(personInfo.housingEligible);
            sql.Append("', bothEmployee ='");
            sql.Append(personInfo.bothEmployee);
            sql.Append("', evaluation ='");
            sql.Append(personInfo.evaluation);
            sql.Append("', curHousingAddr ='");
            sql.Append(personInfo.curHousingAddr.addr);
            sql.Append("', marital ='");
            sql.Append((int)personInfo.marital);
            sql.Append("', curHousingArea ='");
            sql.Append(personInfo.curHousingAddr.area);
            sql.Append("', workStatus ='");
            sql.Append((int)personInfo.workStatus);
            sql.Append("', withArmy ='");
            sql.Append((int)personInfo.withArmy);
            sql.Append("', mateNameInitial ='");
            sql.Append(Spell.MakeSpellCode(personInfo.mateInfo.name, SpellOptions.FirstLetterOnly));
            sql.Append("' where idperson ='");
            sql.Append(personInfo.idperson.ToString());
            sql.Append("'");

            return sql.ToString();
        }

        //只更新当前住房信息
        public string UpdateOnlyForHousingInfo(PersonInfo personInfo)
        {
            StringBuilder sql = new StringBuilder("update person set ", 64);

            sql.Append("housingInfo ='");
            sql.Append(GetHousingInfoString(personInfo.housingInfo));
            sql.Append("' ");

            sql.Append(", formerHousingId ='");
            sql.Append(GetFormerHousingIdString(personInfo.formerHousingId));
            sql.Append("' ");
            sql.Append("where idperson ='");
            sql.Append(personInfo.idperson.ToString());
            sql.Append("'");

            return sql.ToString();
        }

        //更新双职工标识
        public string UpdateOnlyForEmployee(PersonInfo personInfo, int bothEmpFlag)
        {
            StringBuilder sql = new StringBuilder("update personsortinfo set ", 64);

            sql.Append("sortMateIsEmployee ='");
            sql.Append(bothEmpFlag);

            sql.Append("' where idperson ='");
            sql.Append(personInfo.idperson.ToString());
            sql.Append("'");

            return sql.ToString();
        }

        public string GetHousingInfoString(List<HousingOccupancyInfo> infos)
        {
            if (infos == null || infos.Count == 0)
                return null;

            StringBuilder sql = new StringBuilder("#");

            foreach (HousingOccupancyInfo info in infos)
            {
                sql.Append(info.id.ToString());
                sql.Append("*");
                sql.Append(info.checkinTime.ToShortDateString());
                sql.Append("#");
            }

            return sql.ToString();
        }

        public string GetHousingInfoQueryString(List<HousingOccupancyInfo> infos)
        {
            if (infos == null || infos.Count == 0)
                return null;

            StringBuilder sql = new StringBuilder(" ");

            foreach (HousingOccupancyInfo info in infos)
            {
                sql.Append("idhousing ='");
                sql.Append(info.id.ToString());
                sql.Append("' or ");
            }
            //去除最后一个" or "
            sql.Remove(sql.Length - 4, 4);

            return sql.ToString();
        }

        public string GetFormerHousingIdString(List<int> formerHousingId)
        {
            if (formerHousingId == null || formerHousingId.Count == 0)
                return null;

            StringBuilder sql = new StringBuilder("#");

            foreach (int id in formerHousingId)
            {
                sql.Append(id.ToString());
                sql.Append("#");
            }

            return sql.ToString();
        }

        public string InsertSql(PersonSortInfo info)
        {
            string sqlHeader = "insert into personsortinfo (idperson, sortGrade, sortGradeWeight, sortGradeTime, sortCurGradeBeforeTime, sortWorkTime, sortSignupTime, sortMateName, sortEvaluationWeight, sortUnitWeight) ";

            StringBuilder sql = new StringBuilder(sqlHeader, 512);

            sql.Append("values ('");
            sql.Append(info.idperson);
            sql.Append("', '");
            sql.Append(info.sortGrade);
            sql.Append("', '");
            sql.Append(info.sortGradeWeight);
            sql.Append("', '");
            sql.Append(info.sortGradeTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(info.sortCurGradeBeforeTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(info.sortWorkTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(info.sortSignupTime.ToShortDateString());
            sql.Append("', '");
            sql.Append(info.sortMateName);
            sql.Append("', '");
            sql.Append(info.sortEvaluationWeight);
            sql.Append("', '");
            sql.Append(info.sortUnitWeight);
            sql.Append("')");

            return sql.ToString();
        }

        public string UpdateSql(PersonSortInfo info)
        {
            string sqlHeader = "update personsortinfo set ";

            StringBuilder sql = new StringBuilder(sqlHeader, 512);

            sql.Append("idperson ='");
            sql.Append(info.idperson);
            sql.Append("', sortGrade ='");
            sql.Append(info.sortGrade);
            sql.Append("', sortGradeWeight ='");
            sql.Append(info.sortGradeWeight);
            sql.Append("', sortGradeTime ='");
            sql.Append(info.sortGradeTime.ToShortDateString());
            sql.Append("', sortCurGradeBeforeTime ='");
            sql.Append(info.sortCurGradeBeforeTime.ToShortDateString());
            sql.Append("', sortWorkTime ='");
            sql.Append(info.sortWorkTime.ToShortDateString());
            sql.Append("', sortSignupTime ='");
            sql.Append(info.sortSignupTime.ToShortDateString());
            sql.Append("', sortMateName ='");
            sql.Append(info.sortMateName);
            sql.Append("', sortEvaluationWeight = '");
            sql.Append(info.sortEvaluationWeight);
            sql.Append("', sortUnitWeight = '");
            sql.Append(info.sortUnitWeight);
            sql.Append("' where idperson ='");
            sql.Append(info.idperson.ToString());
            sql.Append("'");

            return sql.ToString();
        }

        public string GetSql(HousingOriginalInfo info)
        {
            string sqlHeader = "insert into housing (housingType, housingFullAddr, streetNo, buildingNo, doorWay, floor, roomNo, housingArea, housingUse, housingProperty, remarks) ";

            StringBuilder sql = new StringBuilder(sqlHeader, 512);

            sql.Append("values ('");
            sql.Append(info.housingType);
            sql.Append("', '");
            sql.Append(info.housingFullAddr);
            sql.Append("', '");
            sql.Append(info.streetNo);
            sql.Append("', '");
            sql.Append(info.buildingNo);
            sql.Append("', '");
            sql.Append(info.doorWay);
            sql.Append("', '");
            sql.Append(info.floor);
            sql.Append("', '");
            sql.Append(info.roomNo);
            sql.Append("', '");
            sql.Append(info.housingArea);
            sql.Append("', '");
            sql.Append(info.housingUse);
            sql.Append("', '");
            sql.Append(info.housingProperty);
            sql.Append("', '");
            sql.Append(info.remarks);
            sql.Append("')");

            return sql.ToString();
        }
    }
}
